using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    /// <summary>
    /// 
    /// </summary>
    public class LocalChargeDetail
    {
        public long ID = 0;
        public long LocalChargeID = 0;
        public long CarrID = 0;
        public long Sort = 0;
        public string Code = "";
        public string MajorCode = "";
        public string InnerCode = "";
        public string Unit = "";
        public int UnitType = 0;
        public int CurrencyType = 0;
        public float BuyPrice = 0;
        public float SellPriceNoVat = 0;
        public float SellPriceWithVat = 0;
        public string Note = "";
        public DateTime LastUpdate = DateTime.Now;
        public const int TypeUSD = 1;
        public const int TypeVND = 2;
        public int ChargeType = 1;
        public const int EXPORTATION = 1;
        public const int IMPORTATION = 2;
        public LocalChargeDetail()
        {
        }
        public LocalChargeDetail(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            if (InnerCode == "OTHC" || InnerCode == "DTHC" || InnerCode == "CIC" || InnerCode == "C/C" || InnerCode == "EBS")
                UnitType = 1;
            if (MajorCode == "EXPORTATION")
                ChargeType = 1;
            else if (MajorCode == "IMPORTATION")
                ChargeType = 2;
            else
                ChargeType = 3;
            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(1, typeof(LocalChargeDetail), "tbl_LocalChargeDetail", "ID", "ID", con, trans));
                // build SQL statement
                sqlCommand = "Insert into tbl_LocalChargeDetail (" +
                    //"ID," +
                             "LocalChargeID," +
                             "CarrID," +
                             "Sort," +
                             "Code," +
                             "MajorCode," +
                             "InnerCode," +
                             "Unit," +
                             "UnitType," +
                             "CurrencyType," +
                             "BuyPrice," +
                             "SellPriceNoVat," +
                             "SellPriceWithVat," +
                             "Note," +
                             "ChargeType," +
                             "LastUpdate" +
                             ") " +
                         " values(" +
                    //ID.ToString() + "," +
                             LocalChargeID.ToString() + "," +
                             CarrID.ToString() + "," +
                             Sort.ToString() + "," +
                             "'" + DBModule.RefineString(Code) + "'," +
                             "'" + DBModule.RefineString(MajorCode) + "'," +
                             "'" + DBModule.RefineString(InnerCode) + "'," +
                             "'" + DBModule.RefineString(Unit) + "'," +
                             UnitType.ToString() + "," +
                             CurrencyType.ToString() + "," +
                             BuyPrice.ToString() + "," +
                             SellPriceNoVat.ToString() + "," +
                             SellPriceWithVat.ToString() + "," +
                             "'" + DBModule.RefineString(Note) + "'," +
                             ChargeType.ToString() + "," +
                             DBModule.RefineDatetime(LastUpdate) + ")";
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
                UserObjectsLog.SaveObjectActionLog("LocalCharges", "add", cn, trans);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                sqlCommand = "Update tbl_LocalChargeDetail set " +
                    //"ID=" + ID.ToString() + "," +
                             "LocalChargeID=" + LocalChargeID.ToString() + "," +
                             "CarrID=" + CarrID.ToString() + "," +
                             "Sort=" + Sort.ToString() + "," +
                             "MajorCode='" + DBModule.RefineString(MajorCode) + "'," +
                             "InnerCode='" + DBModule.RefineString(InnerCode) + "'," +
                             "Code='" + DBModule.RefineString(Code) + "'," +
                             "Unit='" + DBModule.RefineString(Unit) + "'," +
                             "UnitType=" + UnitType + "," +
                             "CurrencyType=" + CurrencyType.ToString() + "," +
                             "BuyPrice=" + BuyPrice.ToString() + "," +
                             "SellPriceNoVat=" + SellPriceNoVat.ToString() + "," +
                             "SellPriceWithVat=" + SellPriceWithVat.ToString() + "," +
                             "Note='" + DBModule.RefineString(Note) + "'," +
                             "ChargeType=" + ChargeType.ToString() + "," +
                             "LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + "" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
                UserObjectsLog.SaveObjectActionLog("LocalCharges", "edit", cn, trans);
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            //Buil SQL Statement
            //Run SQL Statement
            string sqlCommand = "DELETE FROM tbl_LocalChargeDetail WHERE ID = " + this.ID.ToString() + " limit 1";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
            UserObjectsLog.SaveObjectActionLog("LocalCharges", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            //Build SQL Statement
            //Run SQL Statement
            sqlCommand = "SELECT * FROM tbl_LocalChargeDetail WHERE ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID"))
                    ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("LocalChargeID"))
                    LocalChargeID = long.Parse(dr["LocalChargeID"].ToString());
                if (!dr.IsNull("CarrID"))
                    CarrID = long.Parse(dr["CarrID"].ToString());
                if (!dr.IsNull("Sort"))
                    Sort = long.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("Code"))
                    Code = dr["Code"].ToString();
                if (!dr.IsNull("MajorCode"))
                    MajorCode = dr["MajorCode"].ToString();
                if (!dr.IsNull("InnerCode"))
                    InnerCode = dr["InnerCode"].ToString();
                if (!dr.IsNull("Unit"))
                    Unit = dr["Unit"].ToString();
                if (!dr.IsNull("UnitType"))
                    UnitType = int.Parse(dr["UnitType"].ToString());
                if (!dr.IsNull("CurrencyType"))
                    CurrencyType = int.Parse(dr["CurrencyType"].ToString());
                if (!dr.IsNull("BuyPrice"))
                    BuyPrice = float.Parse(dr["BuyPrice"].ToString());
                if (!dr.IsNull("SellPriceNoVat"))
                    SellPriceNoVat = float.Parse(dr["SellPriceNoVat"].ToString());
                if (!dr.IsNull("SellPriceWithVat"))
                    SellPriceWithVat = float.Parse(dr["SellPriceWithVat"].ToString());
                if (!dr.IsNull("Note"))
                    Note = dr["Note"].ToString();
                if (!dr.IsNull("ChargeType"))
                    ChargeType = int.Parse(dr["ChargeType"].ToString());
                if (!dr.IsNull("LastUpdate"))
                    LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
            }
        }
        public static void GetList(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select tbl_LocalChargeDetail.*,tbl_LocalCharge.CarrID,tbl_LocalCharge.PortID from tbl_LocalChargeDetail " +
                " Left Join tbl_LocalCharge on tbl_LocalChargeDetail.LocalChargeID=tbl_LocalCharge.ID" +
                " where 1=1 ";
            if ((strCondition != "") && (strCondition != null))
            {
                sqlCommand += strCondition;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            else
            {
                sqlCommand = sqlCommand + "ORDER BY CASE InnerCode WHEN 'OTHC' THEN 0" +
                " WHEN 'Seal' THEN 1" +
                " WHEN 'DOC' THEN 2" +
                " WHEN 'Telex' THEN 3" +
                " WHEN 'MNF' THEN 4" +
                " WHEN 'DTHC' THEN 5" +
                " WHEN 'C/C' THEN 6" +

                " WHEN 'DO' THEN 7" +
                " WHEN 'HL' THEN 8" +
                " WHEN 'EBS' THEN 9" +
                " WHEN 'CIC' THEN 10" +
                " WHEN 'LPF' THEN 11" +
                " ELSE 12" +
                " END";
            }
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(sqlCommand, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
        }
        public static void DoSearch(string keywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select `ID` from tbl_LocalChargeDetail where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
        }
    }
}
